﻿/*
   Copyright 2012 University of Southampton

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Diagnostics;
using System.IO;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace uk.ac.soton.ses
{
    public static class HDCDatabaseCreator
    {
        private static string _sqlScriptFileNameAppendString_CreateDatabase = HDCDatabaseCreatorProperties.Default.SQLScriptFileNameAppendString_CreateDatabase;
        private static string _sqlScriptFileNameAppendString_CreateSchema = HDCDatabaseCreatorProperties.Default.SQLScriptFileNameAppendString_CreateSchema;
        private static string _sqlScriptFileNameAppendString_PopulateData = HDCDatabaseCreatorProperties.Default.SQLScriptFileNameAppendString_PopulateData;

        public static void CreateHDCDatabase(string dbServer, string schemaDefinitionToUse, string nameOfDatabaseToCreate)
        {
            string sqlCommand = "";
            string createDatabaseScriptFileName = String.Format("{0}{1}.sql", schemaDefinitionToUse, _sqlScriptFileNameAppendString_CreateDatabase);
            string createSchemaScriptFileName = String.Format("{0}{1}.sql", schemaDefinitionToUse, _sqlScriptFileNameAppendString_CreateSchema);
            string populateDataScriptFileName = String.Format("{0}{1}.sql", schemaDefinitionToUse, _sqlScriptFileNameAppendString_PopulateData);

            //Create Database
            sqlCommand = HDCDatabaseCreator.LoadEmbeddedSQLScript(createDatabaseScriptFileName);
            sqlCommand = sqlCommand.Replace(schemaDefinitionToUse, nameOfDatabaseToCreate);
            HDCDatabaseCreator.ExecuteSQL(dbServer, "master", sqlCommand);

            //Create Schema
            sqlCommand = HDCDatabaseCreator.LoadEmbeddedSQLScript(createSchemaScriptFileName);
            sqlCommand = sqlCommand.Replace(schemaDefinitionToUse, nameOfDatabaseToCreate);
            HDCDatabaseCreator.ExecuteSQL(dbServer, nameOfDatabaseToCreate, sqlCommand);

            //Populate Data
            sqlCommand = HDCDatabaseCreator.LoadEmbeddedSQLScript(populateDataScriptFileName);
            sqlCommand = sqlCommand.Replace(schemaDefinitionToUse, nameOfDatabaseToCreate);
            HDCDatabaseCreator.ExecuteSQL(dbServer, nameOfDatabaseToCreate, sqlCommand);
        }

        private static string LoadEmbeddedSQLScript(string resourceName)
        {
            //Get the current assembly
            Assembly thisAssembly = Assembly.GetExecutingAssembly();
            string assemblyNamespace = typeof(HDCDatabaseCreator).Namespace;
            string resourcePath = String.Format("{0}.{1}", assemblyNamespace, resourceName);

            Trace.WriteLine(String.Format("Loading {0}", resourcePath));
            Trace.WriteLine("GetType(): " + typeof(HDCDatabaseCreator));

            //Load the resource using its fully qualified name otherwise it will not be found
            using (Stream resourceStream = thisAssembly.GetManifestResourceStream(resourcePath))
            {
                //Use a StreamReader to read the contents of the SQL script contained in the resource
                StreamReader sr = new StreamReader(resourceStream);
                return sr.ReadToEnd();
            }
        }

        private static string CreateConnectionString(string dbServer, string dbName)
        {
            //Build SQL Connection string
            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = dbServer;
            connectionStringBuilder.InitialCatalog = dbName;
            connectionStringBuilder.IntegratedSecurity = true;
            connectionStringBuilder.MultipleActiveResultSets = true;
            return connectionStringBuilder.ToString();
        }

        private static void ExecuteSQL(string dbServer, string dbName, string sqlCommandText)
        {
            string connectionString = HDCDatabaseCreator.CreateConnectionString(dbServer, dbName);

            //Log Connection String
            Trace.WriteLine("==== " + DateTime.Now.ToString());
            Trace.WriteLine(String.Format("--\tSQL Connection string:'{0}'", connectionString));
            //Log SQL command
            Trace.WriteLine("--\tSQL command:");
            Trace.WriteLine(sqlCommandText);

            SqlConnection sqlConnection = new SqlConnection(connectionString);
            Trace.WriteLine("--\tGot new SqlConnection");

            //SqlCommand sqlCommand = new SqlCommand(sqlCommandText, sqlConnection);
            //sqlConnection.Open();
            //Trace.WriteLine("--\tConnection now open");
            //sqlCommand.ExecuteNonQuery();

            ServerConnection serverConnection = new ServerConnection(sqlConnection);
            Server server = new Server(serverConnection);

            try
            {
                server.ConnectionContext.ExecuteNonQuery(sqlCommandText);
            }
            catch (Exception e)
            {
                Trace.WriteLine(String.Format("ERROR: {0}", e.Message));

                //Rethrow the exception so the user gets to know the installation did not complete successfully;
                //also, Windows will not list the software on the list of installed software
                throw e;
            }
            finally
            {
                server.ConnectionContext.Disconnect();
            }
        }
    }
}
